﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Collections;
using System.Collections.ObjectModel;
using SYcwpb.Models;
using SYcwpb.Helpers;
using System.Data;
using System.Data.SqlClient;

namespace SYcwpb.Access
{
   
    class AttendAccess
    {
        SqlDataLayer sqlHelper = new SqlDataLayer();

        public bool CheckUserIsDriver(string userName,bool isReplace=false)
        {
            string sql_check ;
            if (!isReplace)
            {
                sql_check = "select user_view_name,user_dep_id as user_type from [system_user] where user_name = '" + userName + "'";
            }
            else
            {
                sql_check = "select user_view_name,user_dep_id as user_type from [system_user] as s left join drivergroup dg on dg.replaceDriver=s.user_id where user_name = '" + userName + "'";
            }
            DataTable dt = sqlHelper.SelectDt(sql_check);
            if (dt == null || dt.Rows.Count < 1)
                return false;

            string _type = dt.Rows[0]["user_type"].ToString();
            //userViewName = dt.Rows[0]["user_view_name"].ToString();

            return _type == "7" ? true : false;
        }

        ///// <summary>
        ///// 判断司机本事是否是作为替代司机
        ///// </summary>
        ///// <param name="driverId"></param>
        ///// <param name="date"></param>
        ///// <param name="lineId"></param>
        ///// <returns></returns>
        //public bool IsReplaceDriver(string driverId,string date,string lineId)
        //{
        //    string sql = "select count(s1.user_name) from [system_user] as s left join drivergroup as dg on dg.replaceDriver=s.user_id where s.user_name="+driverId+
        //        " and dgrp_line="+lineId+" and dgrp_date='"+date+"'";
        //    object obj = sqlHelper.GetSingle(sql);
        //    if (obj != null && Convert.ToInt32(obj) > 0)
        //        return true;
        //    else
        //        return false;
        //}

        /// <summary>
        /// 获取出勤司机的信息，包括本职出勤和代班出勤
        /// </summary>
        /// <param name="driverId"></param>
        /// <param name="date"></param>
        /// <param name="lineId"></param>
        /// <returns></returns>
        public DataTable GetDutyDriver(string driverId, string date, string lineId)
        {
            string sql = "select dg.*,s.user_name as dutyId,s.user_view_name as dutyname1,"+
                " s1.user_name as replaceId,s1.user_view_name as replacename1,Cr.attenTime as planTime from drivergroup as dg left join [system_user] as s on s.user_id=dg.dgrp_user_id " +
                " left join [system_user] as s1 on s1.user_id=dg.replacedriver "+
                " left join DriverCombine as dc on dc.id=dg.dgrp_dcom_id " +
                " left join CrossingRoad as cr on dc.dcom_cr_number = Cr.ID " +
                " where (s.user_name=" + driverId + " or s1.user_name=" + driverId + ") and dgrp_line=" + lineId + " and dgrp_date='" + date + "'";
            return sqlHelper.SelectDt(sql);
        }

        /// <summary>
        /// 获取带班司机的工号
        /// 判断是否是本职出勤还是代班出勤
        /// 如果是本职出勤则返回自己，如果是代班出勤则返回被替代司机
        /// 
        /// select s.user_name from [system_user] as s left join drivergroup as dg on dg.dgrp_user_id=s.user_id where s.user_name=driverid and (() )
        /// </summary>
        /// <param name="driverId"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public string GetReplaceDriver(string driverId,string date,string lineId)
        {
            var dt = sqlHelper.SelectDt("select s1.user_name from [system_user] as s left join drivergroup as dg on dg.replaceDriver=s.user_id "+
                " left join [system_user] as s1 on s1.user_id=dg.dgrp_user_id where s.user_name=" + driverId + " and dgrp_line=" + lineId + " and dgrp_date='" + date + "'");
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
                return null;
        }
        public bool CheckPasWrdValidation(string userName, string pasWrd)
        {
            StringBuilder sb = GlobalVariables.DecryptCode(pasWrd);

            string sql_check = "select count(*) from [system_user] where user_name = '" + userName + "' and user_password = '" + sb.ToString() + "'";
            return Convert.ToInt32(sqlHelper.GetSingle(sql_check)) > 0;
        }

        public string GetDriverClass(string userName)
        {
            string sql_getClass = "select" +
                                    " S.user_name ,"+
                                    " S.user_view_name,"+
                                    " Dcom.dcom_team as team,"+
                                    " Ctn.ctn_cls_id as cls_id"+
                                    " from DriverGroup Dg left join [system_user] S on Dg.dgrp_user_id = S.user_id"+
                                    " left join DriverCombine Dcom on Dg.dgrp_dcom_id = Dcom.ID"+
                                    " left join ClassTurn Ctn on (Ctn.line = Dcom.dcom_lineid and Ctn.ctn_team = Dcom.dcom_team)"+
                                    " where S.user_name = '" + userName + "'and dcom.dcom_date='"+GetData()+"'";

            DataTable dt = sqlHelper.SelectDt(sql_getClass);
            if (null != dt && dt.Rows.Count > 0)
                return dt.Rows[0]["cls_id"].ToString();

            return null;
        }

        /// <summary>
        /// 判断该司机是否已经出勤过，返回其出勤时间
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public bool CheckHasAttend(string userName,ref string attendTime)
        {
            string sql_check = "select user_id,user_name,user_view_name, dgrp_attendtime"+
                                " from DriverGroup left join [system_user]  on dgrp_user_id = user_id" +
                                " where user_name = '" + userName + "'";
            DataTable dt = sqlHelper.SelectDt(sql_check);
            if (null == dt || dt.Rows.Count < 1)
                return false;

            attendTime = dt.Rows[0]["dgrp_attendtime"].ToString();

            return !string.IsNullOrEmpty(attendTime);
        }

        public bool UpdateAttenPBY(string userName,string pby)
        {
            string sql_update = "update DriverGroup set dgrp_pbyInfo = '" + pby + "' " +
                                          " where dgrp_user_id = (select user_id from [system_user] where user_name = '" + userName + "')  and dgrp_line='" + CONSTDATA.UserModel.Line + "'";
            //string sql_update = "update DriverGroup set dgrp_pbyInfo = '" + pby + "' " +
            //                             " where dgrp_user_id = (select user_id from [system_user] where user_name = '" + userName + "')  ";
            return sqlHelper.ExecuteSql(sql_update) > 0; 
        }

        public bool UpdateDTQ(string userName, string pby)
        {
            string sql_update = "update DriverGroup set dgrp_dtq = '" + pby + "' " +
                                          " where dgrp_user_id = (select user_id from [system_user] where user_name = '" + userName + "')  and dgrp_line='" + CONSTDATA.UserModel.Line + "'";
            //string sql_update = "update DriverGroup set dgrp_dtq = '" + pby + "' " +
            //                              " where dgrp_user_id = (select user_id from [system_user] where user_name = '" + userName + "') ";
            return sqlHelper.ExecuteSql(sql_update) > 0;
        }


        /// <summary>
        /// 现在只支持两个司机：
        /// 1）主、副司机正常出勤下，使用后来司机的出勤时间作为最终出勤时间；
        /// 2）若两司机有某一司机迟到，则分开记录其出勤时间；
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="attenT"></param>
        /// <returns></returns>
        public bool UpdateAttendTime(string userName, ref string attenT)
        {
            attenT = DateTime.Now.ToString("HH:mm:ss");
            string sql_update = "update DriverGroup set dgrp_attendtime = '" + attenT + "' " +
                                " where dgrp_user_id = (select user_id from [system_user] where user_name = '" + userName + "')";
            string sql_updateParner = "";

            string sql_getAttendInfoSeft = "select" +
                                            " cr.attenTime as planT," +
                                            " dgrp_attendtime as actualT" +
                                            " from [system_user] left join DriverGroup on user_id = dgrp_user_id" +
                                            " left join DriverCombine Dcom on dgrp_dcom_id = Dcom.ID" +
                                            " left join CrossingRoad Cr on dcom_cr_number = Cr.ID" +
                                            " where user_name = '" + userName + "'";

            DataTable seftDT = sqlHelper.SelectDt(sql_getAttendInfoSeft);
            if (seftDT == null || seftDT.Rows.Count < 1)
                return false;

            ArrayList sqllist = new ArrayList();

            string seftPlan = seftDT.Rows[0]["planT"].ToString();
            string seftActual = seftDT.Rows[0]["actualT"].ToString();
            //这里条件设置为true是因为现在更改为不更新同伴的出勤信息
            if (true ||string.IsNullOrEmpty(seftPlan) || seftActual.CompareTo(seftPlan) > 0) //如果本人的计划出勤时间是空的，或者迟到了，没必要查看另一个司机的实际出勤信息，直接更新返回
                return sqlHelper.ExecuteSql(sql_update) > 0;
            else  //查找同伴司机的出勤信息
            {
                string sql_parnerAttneInfo = "select user_id,user_name,user_view_name," +
                                            " cr.attenTime as planT," +
                                            " dgrp_attendtime as actualT" +
                                            " from [system_user] left join DriverGroup on user_id = dgrp_user_id" +
                                            " left join DriverCombine Dcom on dgrp_dcom_id = Dcom.ID" +
                                            " left join CrossingRoad Cr on dcom_cr_number = Cr.ID" +
                                            " where dgrp_dcom_id = " +
                                            " (select dgrp_dcom_id" +
                                            " from [system_user] left join DriverGroup on user_id = dgrp_user_id" +
                                            " where user_name = '" + userName + "') and user_name <> '" + userName + "'";

                DataTable parnerDT = sqlHelper.SelectDt(sql_parnerAttneInfo);
                if (null != parnerDT && parnerDT.Rows.Count > 0)
                {
                    string parnerActual = parnerDT.Rows[0]["actualT"].ToString();
                    if (!string.IsNullOrEmpty(parnerActual) && parnerActual.CompareTo(seftPlan) < 1) //如果这个司机是没有迟到的,更新同伴司机的实际出勤时间
                    {
                        string parnerID = parnerDT.Rows[0]["user_id"].ToString();
                        sql_updateParner = "update DriverGroup set dgrp_attendtime = '" + attenT + "' " +
                                " where dgrp_user_id = '" + parnerID + "'";

                        sqllist.Add(sql_update);
                        sqllist.Add(sql_updateParner);

                        return sqlHelper.ExecuteSqlTran(sqllist);
                    }
                }
            }
            return sqlHelper.ExecuteSql(sql_update) > 0;  
        }

        public bool UpdateOffWorkTime(string userName, ref string offT)
        {
            offT = DateTime.Now.ToString("HH:mm:ss");

            string sql_update = "update DriverGroup set dgrp_outtime = '" + offT + "' " +
                                " where dgrp_user_id = (select user_id from [system_user] where user_name = '" + userName + "')";

            return sqlHelper.ExecuteSql(sql_update) > 0;
        }

        /// <summary>
        /// 获取版本号
        /// </summary>
        /// <returns></returns>
        public string GetVersion()
        {

            string sql_update = "select * from SoftVersion where Code='kfd'";
            DataTable dt = sqlHelper.SelectDt(sql_update);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["sVersion"].ToString();
            }
            else
            {
                return "[获取版本失败]";
            }
        }

        public string GetData()
        {
            DateTime dt1 = DateTime.Now;//当前时间
            DateTime dt2 = Convert.ToDateTime(DateTime.Now.ToShortDateString() + " 02:00:00");//更新时间
            if (dt1 > dt2)
            {
                return DateTime.Now.ToString("yyyy-MM-dd");
            }
            else
            {
                return DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
            }
            return DateTime.Now.ToString("yyyy-MM-dd");

        }
    }
}
